package com.szy.web.dao;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.szy.web.model.Comment;
import com.szy.web.model.Page;


public class CommentDAO
{
	SqlManager manager;
	String sql = "";
	ResultSet rs;
	
	public CommentDAO() throws IOException, ClassNotFoundException
	{
		manager = SqlManager.createInstance();
	}
	
	
	/**
	 * 
	 * @param currentPage
	 * @param pageSize
	 * @return 把分页查询出来的内容放到page里面。
	 * @throws Exception
	 */
	public  Page showComment(int currentPage, int pageSize, int nid ) {
		List<Comment> list = new ArrayList<Comment>();
		Page page = new Page();
	//	int n = 0;
		int begin = 0;
		begin = ( currentPage - 1 ) * pageSize ;
		sql = "SELECT * FROM t_comment WHERE nid = ? AND deleted = 0  limit ?,?" ;
		//sql = "SELECT * FROM t_comment ORDER BY nid = 1 ASC, deleted = 0 ASC, cid DESC  LIMIT ?,?";
		Object[] params = new Object[]{nid ,begin, pageSize };
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql,params);
			while (rs.next()) 
			{
				Comment comment = new  Comment();
				comment.setCid( rs.getInt("cid"));
				comment.setNid(rs.getInt("nid"));
				comment.setPtime(rs.getString("ptime"));
				comment.setRegion(rs.getString("region"));
				comment.setContent(rs.getString("content"));
				comment.setOpposeCount(rs.getInt("opposecount"));
				comment.setSupportCount(rs.getInt("supportcount"));
				comment.setDeleted(rs.getBoolean("deleted"));
				System.out.println("content = "+ rs.getString("content"));
				list.add(comment);
			}
			page.setList(list);
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
//			sql = "SELECT * FROM t_comment WHERE nid = ? AND deleted = 0";
//			Object[] params1 = new Object[]{ nid };
//			rs = manager.executeQuery(sql,params1);
//			while(rs.next())
//			{
//				n++;
//			}
			page.setTotalRecords(list.size());
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return page;
	}
	public  Page showComment(int currentPage, int pageSize, String region ) {
		List<Comment> list = new ArrayList<Comment>();
		Page page = new Page();
		int n = 0;
		int begin = 0;
		begin = ( currentPage - 1 ) * pageSize ;
		sql = "SELECT * FROM t_comment WHERE region = ? AND deleted = 0  limit ?,?" ;
		Object[] params = new Object[]{region ,begin, pageSize };
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql,params);
			while (rs.next()) 
			{
				Comment comment = new  Comment();
				comment.setCid( rs.getInt("cid"));
				comment.setNid(rs.getInt("nid"));
				comment.setPtime(rs.getString("ptime"));
				comment.setRegion(rs.getString("region"));
				comment.setContent(rs.getString("content"));
				comment.setOpposeCount(rs.getInt("opposecount"));
				comment.setSupportCount(rs.getInt("supportcount"));
				comment.setDeleted(rs.getBoolean("deleted"));
				System.out.println("content = "+ rs.getString("content"));
				list.add(comment);
			}
			page.setList(list);
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
			sql = "SELECT * FROM t_comment WHERE region = ? AND deleted = 0";
			Object[] params1 = new Object[]{ region };
			rs = manager.executeQuery(sql,params1);
			while(rs.next())
			{
				n++;
			}
			page.setTotalRecords(n);
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return page;
	}
	public boolean deleteComment( int cid ) {
		sql = "update  t_comment set deleted = 1 where cid = ?" ;
		//sql = "delete from t_comment where cid = ?";
		Object[] params = new Object[]{cid };
		try {
			manager.connectDB();
			manager.executeUpdate(sql, params);
			manager.closeDB();
		} catch (SQLException e) 
		{
			e.printStackTrace();		
			return false;
		}
		return true;
	}

	
	
	
	
	
	
	/**
	 * 获取回复信息
	 * @param nid 新闻编号
	 * @param startRid 起始ID
	 * @param count 返回数量
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<Comment> getComments(int nid,int startCid,int count) throws SQLException
	{
		ArrayList<Comment> list = new ArrayList<Comment>();
		sql = "select cid,nid,ptime,region,content,supportcount,opposecount from t_comment where nid=? and deleted=false order by ptime desc limit ?,?";
		Object[] params = new Object[]
		{ nid, startCid, count };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		while (rs.next())
		{
			Comment comment = new Comment();
			comment.setCid(rs.getInt("cid"));
			comment.setNid(rs.getInt("nid"));
			comment.setPtime(rs.getString("ptime"));
			comment.setRegion(rs.getString("region"));
			comment.setContent(rs.getString("content"));
			comment.setSupportCount(rs.getInt("supportcount"));
			comment.setOpposeCount(rs.getInt("opposecount"));
			list.add(comment);
		}
		manager.closeDB();
		return list;
	}
	
	/**
	 * 保存新评论
	 * @param nid 新闻编号
	 * @param ptime 发表时间
	 * @param region 地区
	 * @param content 内容
	 * @throws SQLException
	 */
	public void addComment(int nid,String ptime,String region,String content) throws SQLException
	{
		sql = "INSERT INTO t_comment (nid,ptime,region,content) VALUES (?,?,?,?)";
		Object[] params = new Object[] { nid, ptime, region,content };
		manager.connectDB();
		manager.executeUpdate(sql, params);
		manager.closeDB();
	}
	
	/**
	 * 获取指定新闻评论数量
	 * @param nid
	 * @return
	 * @throws SQLException
	 */
	public long getSpecifyNewsCommentsCount(int nid) throws SQLException
	{
		long count = 0;
		sql = "select count(cid) as count from t_comment where nid=?";
		Object[] params = new Object[]{ nid };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		if (rs.next())
		{
			count = rs.getLong("count");
		}
		return count;
	}
}
